///该脚本使用场景：Pgsql数据库作为DRS同步任务的源端数据源时，创建源端表,创建表触发器，增删改表数据的场景。

///Pgsql，定义date_time字段在数据发生insert操作时会同步更新为当前系统时间
CREATE TABLE "public"."pgsql_source"(
	"id" int8 NOT NULL,
	"date_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
	"field01" bpchar(1) NULL,
	"field02" bpchar(1) NULL,
	"field03" bpchar(1) NULL,
	"field04" bpchar(1) NULL,
	"field05" bpchar(1) NULL,
	"field06" bpchar(1) NULL,
	"field07" bpchar(1) NULL,
	"field08" bpchar(1) NULL,
	"field09" bpchar(1) NULL,
	"field10" bpchar(1) NULL
);

///触发器函数,主要作用是当源端表数据发生update操作时，date_time字段会同步更新到当前系统时间
CREATE OR REPLACE FUNCTION tri_update_func() RETURNS TRIGGER AS
           $$
           DECLARE
           BEGIN
                 new.date_time=CURRENT_TIMESTAMP;
                 return new;
           END
           $$ LANGUAGE PLPGSQL;
           
///调用触发器函数给源端表添加触发器
CREATE TRIGGER update_trigger
           BEFORE UPDATE ON pgsql_source   
           FOR EACH ROW
           EXECUTE PROCEDURE tri_update_func();

///循坏新增插入1000000数据
do $$
declare i integer = 1;
begin

  while i < 1000001 loop
  insert into "public"."pgsql_source" (id,field01,field02,field03,field04,field05,field06,field07,field08,field09,field10) values(i,'q','w','e','r','t','y','u','i','o','p') ;
  i= i+1;
  end loop;
end 
$$;


///修改id等于1-100000的数据
update "public"."pgsql_source" set field01='F' where id>=1 and id<=100000;

///删除id等于100001-200000的数据
delete from "public"."pgsql_source" where id>=100001 and id<=200000;

#PostgreSQL->CDM->DWS测试场景：
#针对一个源端数据库到一个目标库，针对单表进行测试，表预置30个字段，预存1000万条数据。
#pgsql创建一个带主键表，同时插入30个字段，表名为pgsql_source：
CREATE TABLE pgsql_source(
	t_id serial primary key,
	t_datetime timestamp(1) DEFAULT now() NOT NULL,
	t_field01 char(100),
	t_field02 char(100),
	t_field03 char(100),
	t_field04 char(100),
	t_field05 char(100),
	t_field06 char(100),
	t_field07 char(100),
	t_field08 char(100),
	t_field09 char(100),
	t_field10 char(100),
	t_field11 char(100),
	t_field12 char(100),
	t_field13 char(100),
	t_field14 char(100),
	t_field15 char(100),
	t_field16 char(100),
	t_field17 char(100),
	t_field18 char(100),
	t_field19 char(100),
	t_field20 char(100),
	t_field21 char(100),
	t_field22 char(100),
	t_field23 char(100),
	t_field24 char(100),
	t_field25 char(100),
	t_field26 char(100),
	t_field27 char(100),
	t_field28 char(100)
);



#pgsql使用循环翻倍的方式，重复插入1*(2^19)行数据，使用脚本前先手动插入一行数据：
do $$
declare
i integer :=1;
begin
    while i < 20 loop
     INSERT INTO pgsql_source(t_field01,t_field02,t_field03,t_field04,t_field05,t_field06,t_field07,t_field08,t_field09,t_field10,t_field11,t_field12,t_field13,t_field14,t_field15,t_field16,t_field17,t_field18,t_field19,t_field20,t_field21,t_field22,t_field23,t_field24,t_field25,t_field26,t_field27,t_field28
			)
		select t_field01,t_field02,t_field03,t_field04,t_field05,t_field06,t_field07,t_field08,t_field09,t_field10,t_field11,t_field12,t_field13,t_field14,t_field15,t_field16,t_field17,t_field18,t_field19,t_field20,t_field21,t_field22,t_field23,t_field24,t_field25,t_field26,t_field27,t_field28 from pgsql_source;
		commit;
       i := i+1;
    end loop;
end $$;



#pgsql使用循环插入的方式，重复插入数据10000000次，数据为随机生成的三个大写字母，batch_insert_proc()为存储过程名称：
create or replace function batch_insert_proc() returns void as 
$$
declare
num INT = 10000000;
begin
	while num > 0 loop
		insert into pgsql_source(t_datetime,t_field01,t_field02,t_field03,t_field04,t_field05,t_field06,t_field07,t_field08,t_field09,t_field10,t_field11,t_field12,t_field13,t_field14,t_field15,t_field16,t_field17,t_field18,t_field19,t_field20,t_field21,t_field22,t_field23,t_field24,t_field25,t_field26,t_field27,t_field28)
        values(now(),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),
               repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),
               repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3),repeat(chr(int4(random()*26)+65),3));
		num = num -1;
	end loop;
exception
	when others then
	raise exception'(%)',SQLERRM;
end;
$$ language plpgsql;
select batch_insert_proc();
